﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace AchieveCommon
{
    /// <summary>
    /// SQL分页帮助类
    /// </summary>
    public class SqlPagerHelper
    {
        /// <summary>
        /// 获取分页数据（单表分页）,使用存储过程，仅用于ieman数据库
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要取的列名（逗号分开）</param>
        /// <param name="order">排序</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总记录数</param>
        public static DataTable GetPager(string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        {
            SqlParameter[] paras = { 
                                   new SqlParameter("@tableName",SqlDbType.NVarChar,100),
                                   new SqlParameter("@columns",SqlDbType.NVarChar,500),
                                   new SqlParameter("@order",SqlDbType.NVarChar,100),
                                   new SqlParameter("@pageSize",SqlDbType.Int),
                                   new SqlParameter("@pageIndex",SqlDbType.Int),
                                   new SqlParameter("@where",SqlDbType.NVarChar,2000),
                                   new SqlParameter("@totalCount",SqlDbType.Int)
                                   };
            paras[0].Value = tableName;
            paras[1].Value = columns;
            paras[2].Value = order;
            paras[3].Value = pageSize;
            paras[4].Value = pageIndex;
            paras[5].Value = where;
            paras[6].Direction = ParameterDirection.Output;   //输出参数
            paras[6].IsNullable = false;

            DataTable dt = SqlHelper.GetDataTable(SqlHelper.connStr, CommandType.StoredProcedure, "sp_Pager", paras);
            totalCount = Convert.ToInt32(paras[6].Value);   //赋值输出参数，即当前记录总数           
            //totalCount = 1;
            //int totalCount;
            //int.TryParse(paras[6].Value, out totalCount);
            return dt;
        }
        /// <summary>
        /// 获取分页数据（单表分页，不使用存储过程）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要取的列名（逗号分开）</param>
        /// <param name="order">排序</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总记录数</param>
        public static DataTable GetPagerBySQL(string connStr, string tableName, string columns, string order, int pageSize, int pageIndex, string where, out int totalCount)
        {
            SqlParameter[] paras = { 
                                   new SqlParameter("@tableName",SqlDbType.NVarChar,100),
                                   new SqlParameter("@columns",SqlDbType.NVarChar,500),
                                   new SqlParameter("@order",SqlDbType.NVarChar,100),
                                   new SqlParameter("@pageSize",SqlDbType.Int),
                                   new SqlParameter("@pageIndex",SqlDbType.Int),
                                   new SqlParameter("@where",SqlDbType.NVarChar,2000),
                                   new SqlParameter("@totalCount",SqlDbType.Int)
                                   };
            paras[0].Value = tableName;
            paras[1].Value = columns;
            paras[2].Value = order;
            paras[3].Value = pageSize;
            paras[4].Value = pageIndex;
            paras[5].Value = where;
            paras[6].Direction = ParameterDirection.Output;   //输出参数
            paras[6].IsNullable = false;
            string sql = @"declare @beginIndex int,@endIndex int,@sqlresult nvarchar(2000),@sqlGetCount nvarchar(2000)
                            set @beginIndex = (@pageIndex - 1) * @pageSize + 1
                            set @endIndex = (@pageIndex) * @pageSize
                            set @sqlresult = 'select '+@columns+' from (
                            select row_number() over(order by '+ @order +')
                            as Rownum,'+@columns+'
                            from '+@tableName+' where '+ @where +') as T
                            where T.Rownum between ' + CONVERT(varchar(max),@beginIndex) + ' and ' + CONVERT(varchar(max),@endIndex);
                            set @sqlGetCount = 'select @totalCount = count(*) from '+@tableName+' where ' + @where
                            exec(@sqlresult)
                            exec sp_executesql @sqlGetCount,N'@totalCount int output',@totalCount output";
            DataTable dt = SqlHelper.GetDataTable(connStr, CommandType.Text, sql, paras);
            totalCount = Convert.ToInt32(paras[6].Value);   //赋值输出参数，即当前记录总数
            return dt;
        }
        /// <summary>
        /// 获取分页数据（多表分页，不使用存储过程）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">要取的列名（逗号分开）</param>
        /// <param name="order">排序</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总记录数</param>
        public static DataTable GetPagerBySQL(string connStr,string tableName, string outColumns,string innerColumns, int pageSize, int pageIndex, string where, string order, out int totalCount)
        {
            SqlParameter[] paras = { 
                                   new SqlParameter("@tableName",SqlDbType.NVarChar,500),
                                   new SqlParameter("@outColumns",SqlDbType.NVarChar,1000),
                                   new SqlParameter("@order",SqlDbType.NVarChar,500),
                                   new SqlParameter("@pageSize",SqlDbType.Int),
                                   new SqlParameter("@pageIndex",SqlDbType.Int),
                                   new SqlParameter("@where",SqlDbType.NVarChar,2000),
                                   new SqlParameter("@innerColumns",SqlDbType.NVarChar,1000),
                                   new SqlParameter("@totalCount",SqlDbType.Int)
                                   
                                   };
            paras[0].Value = tableName;
            paras[1].Value = outColumns;
            paras[2].Value = order;
            paras[3].Value = pageSize;
            paras[4].Value = pageIndex;
            paras[5].Value = where;
            paras[6].Value = innerColumns;
            paras[7].Direction = ParameterDirection.Output;   //输出参数
            paras[7].IsNullable = false;
            string sql = @"declare @beginIndex int,@endIndex int,@sqlresult nvarchar(2000),@sqlGetCount nvarchar(2000)
                            set @beginIndex = (@pageIndex - 1) * @pageSize + 1
                            set @endIndex = (@pageIndex) * @pageSize
                            set @sqlresult = 'select '+@outColumns+' from (
                            select row_number() over(order by '+ @order +')
                            as Rownum,'+@innerColumns+'
                            from '+@tableName+' where '+ @where +') as T
                            where T.Rownum between ' + CONVERT(varchar(max),@beginIndex) + ' and ' + CONVERT(varchar(max),@endIndex);
                            set @sqlGetCount = 'select @totalCount = count(*) from '+@tableName+' where ' + @where
                            exec(@sqlresult)
                            exec sp_executesql @sqlGetCount,N'@totalCount int output',@totalCount output";
            DataTable dt = SqlHelper.GetDataTable(connStr, CommandType.Text, sql, paras);
            totalCount = Convert.ToInt32(paras[7].Value);   //赋值输出参数，即当前记录总数
            return dt;
        }
//        public static DataTable GetPagerBySQL(string connStr, string tableName, string outColumns, string innerColumns, int pageSize, int pageIndex, string where, string order,string groupby, out int totalCount)
//        {
//            SqlParameter[] paras = { 
//                                   new SqlParameter("@tableName",SqlDbType.NVarChar,500),
//                                   new SqlParameter("@outColumns",SqlDbType.NVarChar,1000),
//                                   new SqlParameter("@order",SqlDbType.NVarChar,500),
//                                   new SqlParameter("@pageSize",SqlDbType.Int),
//                                   new SqlParameter("@pageIndex",SqlDbType.Int),
//                                   new SqlParameter("@where",SqlDbType.NVarChar,2000),
//                                   new SqlParameter("@innerColumns",SqlDbType.NVarChar,1000),
//                                   new SqlParameter("@totalCount",SqlDbType.Int)
                                   
//                                   };
//            paras[0].Value = tableName;
//            paras[1].Value = outColumns;
//            paras[2].Value = order;
//            paras[3].Value = pageSize;
//            paras[4].Value = pageIndex;
//            paras[5].Value = where;
//            paras[6].Value = innerColumns;
//            paras[7].Direction = ParameterDirection.Output;   //输出参数
//            paras[7].IsNullable = false;
//            string sql = @"declare @beginIndex int,@endIndex int,@sqlresult nvarchar(2000),@sqlGetCount nvarchar(2000)
//                            set @beginIndex = (@pageIndex - 1) * @pageSize + 1
//                            set @endIndex = (@pageIndex) * @pageSize
//                            set @sqlresult = 'select '+@outColumns+' from (
//                            select row_number() over(order by '+ @order +')
//                            as Rownum,'+@innerColumns+'
//                            from '+@tableName+' where '+ @where +' group by '+@groupby +') as T
//                            where T.Rownum between ' + CONVERT(varchar(max),@beginIndex) + ' and ' + CONVERT(varchar(max),@endIndex);
//                            set @sqlGetCount = 'select @totalCount = count(*) from '+@tableName+' where ' + @where
//                            exec(@sqlresult)
//                            exec sp_executesql @sqlGetCount,N'@totalCount int output',@totalCount output";
//            DataTable dt = SqlHelper.GetDataTable(connStr, CommandType.Text, sql, paras);
//            totalCount = Convert.ToInt32(paras[7].Value);   //赋值输出参数，即当前记录总数
//            return dt;
//        }
       
    }
}
